SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE [dbo].[usp_SearchParticipants_applyCompany]

	(
	@param_recruitment INT,
	@param_employing_unit NVarChar(50),
	@param_contactperson NVarChar(50),
	@param_is_payment NVarChar(50),
	@param_state NVarChar(50),
	@param_is_visible NVarChar(50),
	@param_type NVARCHAR(50),
	@param_UserID NVARCHAR(50)
	)

AS
	/* SET NOCOUNT ON */
	
	IF @param_type='school'
	BEGIN
	 SET @param_type='校内专场'
	 END
	 ELSE
	 BEGIN
	 IF @param_type='web'
	 BEGIN
	  SET @param_type='网络招聘会'
	 END
	 END

	Declare @Flag1 Bit
	Declare @Flag2 Bit
	Declare @Flag3 BIT
	Declare @Flag4 Bit
	Set @Flag1='False'
	Set @Flag2='False'
	Set @Flag3='False'
	Set @Flag4='False'
	IF @param_is_payment='无'
	begin
	Set @Flag1='True'
	Set @param_is_payment='True'
	end
	IF @param_state='无'
	begin
	Set @Flag2='True'
	Set @param_state='True'
	end
	IF @param_is_visible='无'
	begin
	Set @Flag3='True'
	Set @param_is_visible='True'
	end
	IF @param_recruitment=0
	begin
	Set @Flag4='True'
	end

	SELECT dbo.JOL_Recruitment_Participants_apply.id,topic,employing_unit,booth_quantity,contactperson,is_payment,dbo.JOL_Recruitment_Participants_apply.state 
	from dbo.JOL_Recruitment,dbo.JOL_Recruitment_Participants_apply,dbo.JOL_Employer
	where 
	(@Flag4='true' OR recruitment =@param_recruitment )and
	JOL_Employer_Name like '%'+@param_employing_unit+'%' and
	contactperson  like '%'+@param_contactperson+'%' and
	(@Flag1='True' or is_payment = @param_is_payment  ) and
	(@Flag2='True' or dbo.JOL_Recruitment_Participants_apply.state =@param_state ) and
	( @Flag3='True' or is_visible =@param_is_visible  ) AND
	recruitment=dbo.JOL_Recruitment.id AND
	employing_unit=JOL_Employer_No AND 
	type= @param_type AND
	dbo.JOL_Recruitment_Participants_apply.user_add=@param_UserID
	ORDER BY  dbo.JOL_Recruitment_Participants_apply.update_time,dbo.JOL_Recruitment_Participants_apply.add_time DESC
	RETURN
	
	
	
GO
